﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AFCEPF.BankApplication.DAL
{
    /// <summary>
    /// Abstract base class for Sql queries generation.
    /// </summary>
    public abstract class AbstractSqlGenerator
    {
        protected DataMapper _mappedObject;

        /// <summary>
        /// ctor
        /// </summary>
        /// <param name="mappedObject">The mapped object.</param>
        public AbstractSqlGenerator(DataMapper mappedObject)
        {
            _mappedObject = mappedObject;
        }

        /// <summary>
        /// Gets the SQL for last generated ID.
        /// </summary>
        /// <returns>the SQL for last generated ID.</returns>
        public abstract string getSqlForLastGeneratedID();


        /// <summary>
        /// Generates the SQL for the SELECT clause.
        /// </summary>
        /// <returns>the SQL for the SELECT clause</returns>
        public virtual string GenerateSqlFor_Select()
        {
            string formatString = "SELECT * FROM {0} WHERE {1} = {2};";

            // On récupère la MappedProperty qui représente la clé primaire.
            MappedProperty primaryKeyProperty = _mappedObject.MappedProperties["Id"];

            string sql = string.Format(formatString,
                                       _mappedObject.GetTableName(),
                                       primaryKeyProperty.PropertyFieldName,
                                       FormatParameter(primaryKeyProperty.PropertyValue));
                                       

            return sql;
        }

        /// <summary>
        /// Generates the SQL for the INSERT clause.
        /// </summary>
        /// <returns>the SQL for the INSERT clause</returns>
        public virtual string GenerateSqlFor_Insert()
        {
            // INSERT INTO TableName VALUES (ValChamp1, .. ValChampX);

            var sb = new StringBuilder();

            sb.AppendFormat("INSERT INTO {0} VALUES (", _mappedObject.GetTableName());


            // on veut .. "NomChampMappé = Valeur propriété"
            var query = from n in _mappedObject.MappedProperties.Values
                        where !n.IsPrimaryKey
                        select new
                        {
                            Result = string.Format("{0}", FormatParameter(n.PropertyValue))
                        };

            // On parcourt maintenant le jeu de résultat 
            // de notre requête LINQ, et on ajoute chaque 
            // élément au StringBuilder, avec un CRLF
            foreach (var generatedString in query)
            {
                sb.AppendFormat("{0}, ", generatedString.Result);
            }

            // on supprime les deux derniers caractères inutiles.
            sb.Remove(sb.Length - 2, 2);
            sb.Append(") ;");

            return sb.ToString();

            
        }

         /// <summary>
        /// Generates the SQL for the UPDATE clause.
        /// </summary>
        /// <returns>the SQL for the UPDATE clause</returns>
        public virtual string GenerateSqlFor_Update()
        {
            var sb = new StringBuilder();

            sb.AppendFormat("UPDATE {0} SET ", _mappedObject.GetTableName());


            // on veut .. "NomChampMappé = Valeur propriété"
            var query = from n in _mappedObject.MappedProperties.Values
                        where !n.IsPrimaryKey
                        select new
                        {
                            Result = string.Format("{0}={1}",
                                                   n.PropertyFieldName,
                                                   FormatParameter(n.PropertyValue))
                        };

            // On parcourt maintenant le jeu de résultat 
            // de notre requête LINQ, et on ajoute chaque 
            // élément au StringBuilder, avec un CRLF
            foreach (var generatedString in query)
            {
                sb.AppendFormat("{0}, ", generatedString.Result);
            }
            
            // on supprime les deux derniers caractères inutiles.
            sb.Remove(sb.Length - 2, 2);

            var primaryKeyProperty = _mappedObject.MappedProperties["Id"];

            sb.AppendFormat(" WHERE {0}={1};", 
                            primaryKeyProperty.PropertyFieldName,
                            FormatParameter(primaryKeyProperty.PropertyValue));

            return sb.ToString();

        }

        /// <summary>
        /// Generates the SQL for the DELETE clause.
        /// </summary>
        /// <returns>the SQL for the DELETE clause</returns>
        public virtual string GenerateSqlFor_Delete()
        {
            const string formatString = "DELETE FROM {0} WHERE {1} = {2};";

            MappedProperty primaryKeyProperty = _mappedObject.MappedProperties["Id"];

            string sql = string.Format(formatString,
                                       _mappedObject.GetTableName(),
                                       primaryKeyProperty.PropertyFieldName,
                                       FormatParameter(primaryKeyProperty.PropertyValue));


            return sql;
        }

        /// <summary>
        /// Formats the parameter for an SQL statement
        /// (for example, strings must be between quotes).
        /// </summary>
        /// <param name="parameterValue">The parameter value.</param>
        /// <returns>The formated parameter</returns>
        protected virtual string FormatParameter(object parameterValue)
        {
            Type parameterType      = parameterValue.GetType();
            const string formatString = "{0}{1}{0}";
            string delimiter        = string.Empty;

            if (parameterType == typeof(string))
            {
                delimiter = "'";
            }
            // Handle other types if needed..
            //else if (parameterType == typeof(DateTime)
            //{
            //}
            else
            {
                delimiter = string.Empty;
            }
            return string.Format(formatString, delimiter ,parameterValue.ToString()) ;
        }

       




    }
}
